<?php

class SJB_ObjectDBManager
{
	/**
	 * @param string $db_table_name
	 * @param SJB_Object $object
	 * @param int $sid
	 * @return bool
	 */
	public static function saveObject($db_table_name, SJB_Object $object, $sid = false)
	{
		$object_sid = $object->getSID();
		if (is_null($object_sid)) {
			if ($sid) {
				if (!SJB_DB::query("INSERT INTO ?w (sid) VALUES($sid)", $db_table_name))
					return false;
				else 	
					$object_sid = $sid;
			}
		 	elseif (!$sid && !$object_sid = SJB_DB::query("INSERT INTO ?w() VALUES()", $db_table_name))
				return false;
			$object->setSID($object_sid);
		}
		
		$object_details = $object->getDetails();
		$object_properties = $object_details->getProperties();
		foreach ($object_properties as $object_property) {
			if (!$object_property->saveIntoBD())
				continue;

			if ($object_property->isComplex()) {
				$complexProperties = $object_property->type->complex->getProperties();
				$parentID = $object_property->getID();
				$keywords = '';
				if ($complexProperties) {
					foreach ($complexProperties as $complexProperty) {
						$complexProperty->setObjectSID($object_property->object_sid);
						$fieldValues = $complexProperty->getValue();
						$property_id = $parentID.":".$complexProperty->getID();
						if (!empty($fieldValues) && is_array($fieldValues)) {
							foreach ($fieldValues as $key => $value) {
								$complexObject = $complexProperty;
								$propertySqlComplexEnum = $key;
								$complexObject->setValue($value);
								$complexObject->setComplexEnum($key);
								$complexObject->setComplexParent($parentID);
								$property_sql_value = $complexObject->getSQLValue();
								$property_sql_add_parameter= $complexObject->getAddParameter();
								$count = SJB_DB::queryValue("SELECT COUNT(*) FROM ?w WHERE object_sid = ?n AND id = ?s AND `complex_enum`=?n", $db_table_name . "_properties", $object_sid, $property_id, $propertySqlComplexEnum);
								$property_exists = $count == 1;

								if ($property_exists)
									SJB_DB::query("UPDATE ?w SET value = ?w, add_parameter = ?s, complex_enum = ?n WHERE object_sid = ?n AND id = ?s AND `complex_enum`=?n", $db_table_name . "_properties", $property_sql_value, $property_sql_add_parameter, $propertySqlComplexEnum, $object_sid, $property_id, $propertySqlComplexEnum);
								else
									SJB_DB::query("INSERT INTO ?w(object_sid, id , value, add_parameter, complex_enum) VALUES(?n, ?s, ?w, ?s, ?n)", $db_table_name . "_properties", $object_sid, $property_id, $property_sql_value, $property_sql_add_parameter, $propertySqlComplexEnum);
							}
							$deleteFields = SJB_DB::query("SELECT * FROM ?w WHERE object_sid = ?n AND id = ?s AND `complex_enum` not in (?l)", $db_table_name . "_properties", $object_sid, $property_id, array_keys($fieldValues));
						}
						if (!empty($deleteFields)) {
							foreach ($deleteFields as $deleteField) {
								if ($complexProperty->getType() == 'complexfile'){
									$uploaded_file_id = $deleteField['value'];
									if ($uploaded_file_id)
										SJB_UploadFileManager::deleteUploadedFileByID($uploaded_file_id);
								}
								SJB_DB::query("DELETE FROM ?w WHERE `id`=?s AND `object_sid`=?n AND `complex_enum`=?n", $db_table_name . "_properties", $property_id, $object_sid, $deleteField['complex_enum']);
							}
						}
					}
				}
			}
			elseif ($object_property->isParent()){
				$childProperties = $object_property->type->child->getProperties();
				$parentID = $object_property->getID();
				$keywords = '';
				if ($childProperties) {
					foreach ($childProperties as $childProperty) {
						$childProperty->setObjectSID($object_property->object_sid);
						$fieldValues = $childProperty->getValue();
						$property_id = $parentID."_".$childProperty->getID();
						$property_sql_value = $childProperty->getSQLValue();
						if ($childProperty->getID() == 'State') {
							$displayAS = $childProperty->display_as;
							$displayAS = $displayAS?$displayAS:'state_name';
							$childProperty->type->list_values = SJB_StatesManager::getStatesNamesByCountry(false, true, $displayAS);
						}
						$keywords .= $childProperty->getKeywordValue() . ' ';
						if ($property_sql_value == "''" && in_array($childProperty->getType(), array('boolean', 'integer', 'float')))
							$property_sql_value = 0;
						SJB_DB::query("UPDATE `?w` SET `?w` = ?w WHERE sid = ?n", $db_table_name, $property_id, $property_sql_value, $object_sid);
					}
				}
				$origValue = $object_property->getValue();
				$object_property->setValue($keywords);
				$property_id = $object_property->getID();
				$property_sql_value = $object_property->getSQLValue();
				$object_property->setValue($origValue);
				SJB_DB::query("UPDATE `?w` SET `?w` = ?w WHERE sid = ?n", $db_table_name, $property_id, $property_sql_value, $object_sid);
			}
			else {
				$property_id = $object_property->getID();
				$property_sql_value = $object_property->getSQLValue();
				$property_sql_add_parameter= $object_property->getAddParameter();
				if ($object_property->isSystem()) {
					if ($property_sql_value == "''" && in_array($object_property->getType(), array('boolean', 'integer', 'float')))
						$property_sql_value = 0;
					SJB_DB::query("UPDATE `?w` SET `?w` = ?w WHERE sid = ?n", $db_table_name, $property_id, $property_sql_value, $object_sid);
					if (!empty($property_sql_add_parameter))
						SJB_DB::query("UPDATE `?w` SET `add_parameter` = ?w WHERE sid = ?n", $db_table_name, $property_sql_add_parameter, $object_sid);
				}
				else {
					if (SJB_DB::table_exists($db_table_name . "_properties")) {
						$property_exists = SJB_DB::queryValue("SELECT COUNT(*) FROM ?w WHERE object_sid = ?n AND id = ?s", $db_table_name . "_properties", $object_sid, $property_id);
						if ($property_exists)
							SJB_DB::query("UPDATE ?w SET value = ?w, add_parameter = ?s WHERE object_sid = ?n AND id = ?s", $db_table_name . "_properties", $property_sql_value, $property_sql_add_parameter, $object_sid, $property_id);
						else
							SJB_DB::query("INSERT INTO ?w(object_sid, id , value, add_parameter) VALUES(?n, ?s, ?w, ?s)", $db_table_name . "_properties", $object_sid, $property_id, $property_sql_value, $property_sql_add_parameter);
					}
				}
			}
		}
		
	}
	
	public static function getObjectInfo($db_table_name, $object_sid)
	{
		$object_info = SJB_DB::query("SELECT * FROM ?w WHERE sid = ?n", $db_table_name, $object_sid);
		$object_info = array_pop($object_info);
		if (empty($object_info))
		    return null;
		    
		
		foreach ($object_info as $key => $val) {
			$locationFields = array("{$key}_Country", "{$key}_State", "{$key}_City", "{$key}_Address", "{$key}_ZipCode");
			if (array_key_exists("{$key}_Country", $object_info) && array_key_exists("{$key}_State", $object_info) && array_key_exists("{$key}_City", $object_info)) {
				$object_info[$key] = array();
				foreach ($locationFields as $field) {
					if (array_key_exists($field, $object_info)) 
						$object_info[$key][str_replace("{$key}_", "", $field)] = $object_info[$field];
				}
			}
		}

		$object_info_properties = array();
        if (SJB_DB::table_exists($db_table_name . "_properties")) {
        	if ($db_table_name == 'listings' || $db_table_name == 'products')
				$object_properties = SJB_DB::query("SELECT *,  if( complex_enum IS NOT NULL, concat( id,':', complex_enum ) , id ) id FROM ?w WHERE object_sid = ?n", $db_table_name . "_properties", $object_sid);
			else
				$object_properties = SJB_DB::query("SELECT * FROM ?w WHERE object_sid = ?n", $db_table_name . "_properties", $object_sid);
			foreach ($object_properties as $object_property) {
				if (strstr($object_property['id'], ':')) {
					$field = explode(':', $object_property['id']);
					if (isset($object_info_properties[$field[0]]) && !is_array($object_info_properties[$field[0]]))
						$object_info_properties[$field[0]] = array();
					if (isset($object_property['add_parameter']) && $object_property['add_parameter'] != '') {
						$object_info_properties[$field[0]][$field[1]][$field[2]]['currency'] = $object_property['add_parameter'];
						$object_info_properties[$field[0]][$field[1]][$field[2]]['add_parameter'] = $object_property['add_parameter'];
						$object_info_properties[$field[0]][$field[1]][$field[2]]['value'] = $object_property['value'];
					}
					else
						$object_info_properties[$field[0]][$field[1]][$field[2]] = $object_property['value'];
				}
				elseif (isset($object_property['add_parameter']) && $object_property['add_parameter'] != ''){
					$object_info_properties[$object_property['id']]['add_parameter'] = $object_property['add_parameter'];
					$object_info_properties[$object_property['id']]['value'] = $object_property['value'];
				}
				else {
					$object_info_properties[$object_property['id']] = $object_property['value'];
				}
			}
		}
		return array_merge($object_info_properties, $object_info);
	}
	
	public static function getObjectsInfoByType($db_table_name)
	{
		$objects_info = SJB_DB::query('SELECT * FROM ?w', $db_table_name);
		foreach ($objects_info as $i => $object_info)
			$objects_info[$i] = SJB_ObjectDBManager::getObjectInfo($db_table_name, $object_info['sid']);
		return $objects_info;
	}

	public static function deleteObjectInfoFromDB($db_table_name, $object_sid)
	{
		if (SJB_DB::table_exists($db_table_name . '_properties')) {
            if (SJB_DB::query('DELETE FROM ?w WHERE object_sid = ?n', $db_table_name . '_properties', $object_sid))
				return SJB_DB::query('DELETE FROM ?w WHERE sid = ?n', $db_table_name, $object_sid);
			return false;
		}
		return SJB_DB::query('DELETE FROM ?w WHERE sid = ?n', $db_table_name, $object_sid);
	}

	public static function deleteObject($db_table_name, $object_sid)
	{
		return SJB_ObjectDBManager::deleteObjectInfoFromDB($db_table_name, $object_sid);
	}

	public static function saveField($db_table_name, $field_table_name, $object, $oldFieldID = false)
	{
		$fieldID = $object->getPropertyValue('id');
		$type = $object->getPropertyValue('type');
		if ($type != 'monetary' && $type != 'complex' && $fieldID != 'ApplicationSettings') {
			$issetField = SJB_DB::query("SHOW COLUMNS FROM `?w` WHERE `Field` like binary ?s", $db_table_name, $fieldID);
			if (!$issetField) {
				$fieldInfo = self::getObjectInfo($field_table_name, $object->getSID());
				$newFieldObject = new SJB_ObjectProperty( $fieldInfo );
				$fieldType = $newFieldObject->type->getSQLFieldType();
				if ($oldFieldID) {
					if (!empty($fieldInfo['parent_sid'])) {
						$parentInfo = self::getObjectInfo($field_table_name, $fieldInfo['parent_sid']);
						if (!empty($parentInfo['id']))
							SJB_DB::query("ALTER TABLE `?w` CHANGE `?w` `?w` ?w", $db_table_name, $parentInfo['id'].'_'.$oldFieldID, $parentInfo['id'].'_'.$fieldID, $fieldType);
					}
					else
						SJB_DB::query("ALTER TABLE `?w` CHANGE `?w` `?w` ?w", $db_table_name, $oldFieldID, $fieldID, $fieldType);
				}
				else {
					SJB_DB::query("ALTER TABLE `?w` ADD `?w` ?w", $db_table_name, $fieldID, $fieldType);
					if ($fieldType != 'TEXT NULL' && $fieldType != 'LONGTEXT NULL')
						SJB_DB::query("ALTER TABLE `?w` ADD INDEX ( `?w` ) ", $db_table_name, $fieldID);
				}
			}
		}
	}
	
	
	public static function saveLocationField($db_table_name, $field_table_name, $object, $oldFieldID = false)
	{
		$fieldID = $object->getPropertyValue('id');
		$issetField = SJB_DB::query("SHOW COLUMNS FROM `?w` WHERE `Field` like binary ?s", $db_table_name, $fieldID);
		if (!$issetField) {
			$fieldInfo = self::getObjectInfo($field_table_name, $object->getSID());
			$newFieldObject = new SJB_ObjectProperty( $fieldInfo );
			$fieldType = $newFieldObject->type->getSQLFieldType();
			if ($oldFieldID) {
				SJB_DB::query("ALTER TABLE `?w` CHANGE `?w` `?w` ?w", $db_table_name, $oldFieldID, $fieldID, $fieldType);
				
				SJB_DB::query("ALTER TABLE `?w` CHANGE `?w` `?w` VARCHAR(255) NULL", $db_table_name, $oldFieldID."_Country", $fieldID."_Country");
				SJB_DB::query("ALTER TABLE `?w` CHANGE `?w` `?w` VARCHAR(255) NULL", $db_table_name, $oldFieldID."_State", $fieldID."_State");
				SJB_DB::query("ALTER TABLE `?w` CHANGE `?w` `?w` VARCHAR(255) NULL", $db_table_name, $oldFieldID."_City", $fieldID."_City");
				SJB_DB::query("ALTER TABLE `?w` CHANGE `?w` `?w` VARCHAR(255) NULL", $db_table_name, $oldFieldID."_ZipCode", $fieldID."_ZipCode");
				if ($db_table_name == 'users') {
					SJB_DB::query("ALTER TABLE `?w` CHANGE `?w` `?w` VARCHAR(255) NULL", $db_table_name, $oldFieldID."_Address", $fieldID."_Address");
				}
			}
			else {
				SJB_DB::query("ALTER TABLE `?w` ADD `?w` ?w", $db_table_name, $fieldID, $fieldType);
				SJB_DB::query("ALTER TABLE `?w` ADD FULLTEXT ( `?w` ) ", $db_table_name, $fieldID);
				
				SJB_DB::query("ALTER TABLE `?w` ADD `?w` VARCHAR(255) NULL", $db_table_name, $fieldID."_Country");
				SJB_DB::query("ALTER TABLE `?w` ADD INDEX ( `?w` ) ", $db_table_name, $fieldID."_Country");
				
				SJB_DB::query("ALTER TABLE `?w` ADD `?w` VARCHAR(255) NULL", $db_table_name, $fieldID."_State");
				SJB_DB::query("ALTER TABLE `?w` ADD INDEX ( `?w` ) ", $db_table_name, $fieldID."_State");
				
				SJB_DB::query("ALTER TABLE `?w` ADD `?w` VARCHAR(255) NULL", $db_table_name, $fieldID."_City");
				SJB_DB::query("ALTER TABLE `?w` ADD INDEX ( `?w` ) ", $db_table_name, $fieldID."_City");
				
				SJB_DB::query("ALTER TABLE `?w` ADD `?w` VARCHAR(255) NULL", $db_table_name, $fieldID."_ZipCode");
				SJB_DB::query("ALTER TABLE `?w` ADD INDEX ( `?w` ) ", $db_table_name, $fieldID."_ZipCode");
				
				if ($db_table_name == 'users') {
					SJB_DB::query("ALTER TABLE `?w` ADD `?w` VARCHAR(255) NULL", $db_table_name, $fieldID."_Address");
					SJB_DB::query("ALTER TABLE `?w` ADD INDEX ( `?w` ) ", $db_table_name, $fieldID."_Address");
				}
			}
		}
	}

	public static function deleteField($db_table_name, $field_name)
	{
		return SJB_DB::query("ALTER TABLE `?w` DROP `?w`", $db_table_name, $field_name);
	}
}

